package com.zeedev.flyingsaucer.data;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.text.TextUtils;
import com.zeedev.flyingsaucer.data.Command.Operator;
import mobi.zeedev.flyingsaucer.common.DataModule.MetaBeer;

import java.util.*;

public class BeerDB {

	public static final String TAG = "BeerDB";

	private String DATABASE_NAME = "-UfoClub.sql.db"; //"FlyingSaucer-UfoClub.sql.db";
	private static final int DATABASE_VERSION = 1;
	private static final String TABLE_NAME = "UFO_CLUB";

	private Context context;
	BeerDataHelperBase beerDataHelper;
	private String fileName;
	private String memberId;

	public BeerDB(Context context) {
		this.context = context;
		beerDataHelper = new BeerDataHelper(this.context, DATABASE_NAME, TABLE_NAME, DATABASE_VERSION );
	}
	
	public BeerDB(Context context, String memberId) {
		this.context = context;
		beerDataHelper = new BeerDataHelper(this.context, memberId + DATABASE_NAME, TABLE_NAME, DATABASE_VERSION );
		fileName = memberId + DATABASE_NAME;
		this.memberId = memberId;
	}
	
	public String getFileName() {
		return fileName;
	}

	public String getIdentifiedMemberId() {
		return this.memberId;
	}
	
	//XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	// CRUD MetaBeer
	//XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

	public void deleteAll() {
		this.beerDataHelper.getWritableDatabase().delete(TABLE_NAME, null, null);
	}

	public void insertAll(List<ContentValues> userBeerListIn, Date now, Integer memberId) {
		SQLiteDatabase curDB =  this.beerDataHelper.getWritableDatabase();
		int countInserted = 0;
		
		curDB.beginTransaction();
		try {
			for (int i = 0; i < userBeerListIn.size(); i++) {
				ContentValues cv = userBeerListIn.get(i);
				if (now != null) {
					cv.put(MetaBeer.INTERNAL_DATE_ADDED, now.getTime());
				}
				
				cv.put(MetaBeer.MEMBER_ID, memberId );
				
				cv.put(MetaBeer.STATUS, MetaBeer.STATUS_ACTIVE);
				curDB.insert(TABLE_NAME, MetaBeer.BEER_ID, cv);
				countInserted++;
			}
			
			curDB.setTransactionSuccessful();
			
		} finally {
			curDB.endTransaction();
		}
		
//		Log.d(TAG, "Inserted " + countInserted + " MetaBeer records.");
		//curDB.close();
	}
	
	public void insertOrUpdateStatus(List<ContentValues> userBeerListIn, Date now, Integer memberId) {
		//Variables
		int countInserted = 0;
		int countUpdated = 0;
		int countInactiveUpdate = 0;

		//setup hashmap so we can look see if there are content values
		Map<Integer, ContentValues> lookupMap = generateKeyMap(userBeerListIn);
		
		SQLiteDatabase curDB =  this.beerDataHelper.getWritableDatabase();
		curDB.beginTransaction();
//		Log.d(TAG, "Transaction begun.");
		try {
			//get cursor to current UserBeer list
			Cursor dbUserBeerList = this.getBeerList();//this.getSimpleUserBeerCursor(null, userDBID);
			Cursor c = dbUserBeerList;
			Boolean doStart = c.moveToFirst();
			if (doStart) {
				do {
					//current record
					//zero is beerId
					int curBeerId = c.getInt(c.getColumnIndex(MetaBeer.BEER_ID));
//					Log.d(TAG,"curBeerId: " + curBeerId);
					
					if (lookupMap.containsKey(curBeerId)) {
						//update record
						ContentValues cv = lookupMap.get(curBeerId);
						//cv.remove(MetaBeer.INTERNAL_DATE_ADDED);
						
						//Make sure the internal added date gets updated when it's "inactive"
						//so new beers can be shown in the new beers activity even if they have
						//not been seen before.
//						Log.d(TAG,"Status: " + c.getString(c.getColumnIndex(MetaBeer.STATUS)));
						if (c.getString(c.getColumnIndex(MetaBeer.STATUS)).equals(MetaBeer.STATUS_INACTIVE)) {
							cv.put(MetaBeer.STATUS, MetaBeer.STATUS_ACTIVE);
//							cv.put(MetaBeer.INTERNAL_DATE_ADDED, now.getTime());
//							Log.d(TAG,"IU: Op: UserBeer: Marked status of one record to ACTIVE.");
						}
						

						
						int res = curDB.update(
								TABLE_NAME, 
								cv, 
								//sqlAnd( 
									makeSqlArg(MetaBeer.BEER_ID,curBeerId),
									//makeSqlArg(UserBeer.USER_ID,userDBID)
									//),
								null);
//						Log.d(TAG, "IU: Op: UserBeer: Updated/inserted: " + res + " records.");
						countUpdated++;
						lookupMap.remove(curBeerId);
						
					} else {
						//UPDATE record WITH INACTIVE STATUS
						ContentValues cv = new ContentValues();
						cv.put(MetaBeer.STATUS, MetaBeer.STATUS_INACTIVE);
						int res = curDB.update(
								TABLE_NAME, 
								cv, 
								//sqlAnd( 
									makeSqlArg(MetaBeer.BEER_ID,curBeerId),
									//makeSqlArg(UserBeer.USER_ID,userDBID)
									//),
								null);
						
//						Log.d(TAG, "IU: Op: UserBeer: Marked status of: " + res + " records to INACTIVE.");					
						countInactiveUpdate++;
					}
					
					//Yield so queries can be processed...	
					//TURN THIS ON API4 changes
					//curDB.yieldIfContendedSafely(500);
					curDB.yieldIfContendedSafely();
					
				} while (dbUserBeerList.moveToNext());
				c.close();
			}
			
			//insert all remaining entries
			for(ContentValues cv: lookupMap.values()) {
				//Update current content values with new record data
				if (now != null) {
					cv.put(MetaBeer.INTERNAL_DATE_ADDED, now.getTime());
				}
				cv.put(MetaBeer.STATUS, MetaBeer.STATUS_ACTIVE);
				cv.put(MetaBeer.MEMBER_ID, memberId );
				
				long res = curDB.insert(TABLE_NAME, MetaBeer.MEMBER_ID, cv);
//				Log.d(TAG, "IU: Op: UserBeer: Inserted: Record Number: " + res + ".");
				countInserted++;
								
				//curDB.yieldIfContendedSafely();
			}
			
			curDB.setTransactionSuccessful();
//			Log.d(TAG, "Transaction successful.");
		} finally {
			curDB.endTransaction();
//			Log.d(TAG, "Transaction completed.");
		}
//		Log.d(TAG, "IU: Total: UserBeer: Inserted: " + countInserted + "; Updated: " + countUpdated + "; Inactive: " + countInactiveUpdate);
		//curDB.close();
	}
	
	//This is necessary for the update to function.
	public Cursor getBeerList() {
		Cursor cursor = this.beerDataHelper.getReadableDatabase().query(
				TABLE_NAME, new String[] { MetaBeer.BEER_ID, MetaBeer.STATUS }, 
				null,
				null, null, null,  
				null);
		return cursor;
	}
	
	//XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	// Cursors / Queries
	//XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

	public Cursor getBeer(Integer beerID) {
		Cursor cursor = this.beerDataHelper.getReadableDatabase().query(
				TABLE_NAME, null, 
				MetaBeer.BEER_ID + " = " + beerID,
				null, null, null,  
				MetaBeer.NAME +" desc");
		return cursor;
	}

	public Cursor getFullBeerList() {
		Cursor cursor = this.beerDataHelper.getReadableDatabase().query(
				TABLE_NAME, null, 
				null,
				null, null, null,  
				MetaBeer.NAME + " desc");
		return cursor;
	}
		
	public Cursor getBeerIdList() {
		Cursor cursor = this.beerDataHelper.getReadableDatabase().query(
				TABLE_NAME, new String[] {MetaBeer.BEER_ID},
				null,
				null, null, null,  
				MetaBeer.BEER_ID);
		return cursor;
	}
	
	public Cursor getGroupCursor2(BeerFilter beerFilter, String groupBy, String sortBy) {
		List<String> fieldList = new ArrayList<String>();
		if (!TextUtils.isEmpty(groupBy)) {
			fieldList.add(groupBy);
		}
		
		return getGroupCursor2(beerFilter,groupBy,sortBy,fieldList);
	}
	
	public Cursor getGroupCursor2(BeerFilter beerFilter, String groupBy, String sortBy, List<String> addedFields) {
		
		//String groupByFilter = beerFilter.getGroupBy();
		//String sortByFilter = beerFilter.getSortBy();
		
		String[] someProjection =  new String[] { };
		List<String> projectionList = new ArrayList<String>();
		//String groupBy = null;
		//String sortBy = null;

		if (groupBy != null) {
//			String groupProperty = null;
//			if (groupBy.contains(" ")) {
//				groupProperty = groupBy.substring(0,groupBy.indexOf(" "));
//			} else {
//				groupProperty = groupBy;
//			}
//			someProjection = new String[] { groupProperty };
			//groupBy = groupByFilter;
			//projectionList.add(trimSort(groupBy));

		}
		
//				
//		if (sortBy != null) {
//			
////			String groupProperty = null;
////			if (sortBy.contains(" ")) {
////				groupProperty = sortBy.substring(0,groupBy.indexOf(" "));
////			} else {
////				groupProperty = sortBy;
////			}
//			//SQLiteQueryBuilder.appendColumns(new StringBuilder(groupProperty), someProjection);
//			//someProjection = new String[] { groupProperty };
//			//groupBy = groupByFilter;
//			projectionList.add(trimSort(sortBy));
//
//		}
		if ( addedFields != null ) {
			projectionList.addAll(addedFields);
		}
		
		someProjection = projectionList.toArray(someProjection);
		
//		if (sortBy != null) {
//			
//		}

	
		
		SQLiteQueryBuilder build =  beerFilter.getSQLBuilder2(someProjection);
		build.setTables(TABLE_NAME);
		Cursor retC = build.query(this.beerDataHelper.getReadableDatabase(), null, null , null, groupBy, null, sortBy, null );//"simpleinternal" 
		return retC; 
	}
	
	public Cursor getNewBeersCursor3(Date originDate, Date dateThen, Date dateNow, BeerFilter filter) {
		//SQLiteQueryBuilder build = new SQLiteQueryBuilder();
		//List<String> queryList = new ArrayList<String>();
		
		if (originDate != null) {
			Command originCmd = new Command(MetaBeer.INTERNAL_DATE_ADDED, String.valueOf(originDate.getTime()), Operator.NOT_EQUAL);
			filter.getCommandList().add(originCmd);
		}
		if (dateThen != null) {
			Command dateThenCmd = new Command(MetaBeer.INTERNAL_DATE_ADDED, String.valueOf(dateThen.getTime()), Operator.GREATER);
			filter.getCommandList().add(dateThenCmd);
		}
		if (dateNow != null) {
			Command dateNowCmd = new Command(MetaBeer.INTERNAL_DATE_ADDED, String.valueOf(dateNow.getTime()), Operator.LESS);
			filter.getCommandList().add(dateNowCmd);
		}
		
		
		
		//build.setDistinct(true);
		//build.setDistinct(true);
		String tret = "round(" + MetaBeer.INTERNAL_DATE_ADDED + "/(1000*60*60*24) ) as " + MetaBeer.SIMPLE_INTERNAL_DATE +" "; //as simpleinternal
		String tret2 = "strftime('%m-%d-%Y', datetime( (" + MetaBeer.INTERNAL_DATE_ADDED +"/1000) , 'unixepoch', 'localtime') ) as " + MetaBeer.SIMPLE_INTERNAL_DATE_2;
		
		SQLiteQueryBuilder build =  filter.getSQLBuilder(TABLE_NAME );
		
		
		//String ret = build.buildQuery(new String[] { tret, MetaBeer.DB_ID, MetaBeer.BEER_ID, MetaBeer.TASTED, MetaBeer.STATUS, MetaBeer.INTERNAL_DATE_ADDED  }, null, null, "simpleinternal", null, null, null);
		Cursor retC = build.query(
			this.beerDataHelper.getReadableDatabase(), 
			new String[] { tret, tret2, MetaBeer.DB_ID, MetaBeer.BEER_ID, MetaBeer.TASTED, MetaBeer.STATUS, MetaBeer.INTERNAL_DATE_ADDED  },
			null , 
			null, 
			MetaBeer.SIMPLE_INTERNAL_DATE_2,//MetaBeer.SIMPLE_INTERNAL_DATE_2,//, 
			null, 
			MetaBeer.SIMPLE_INTERNAL_DATE_2 + " desc",
			null );
		
		//DatabaseUtils.dumpCursor(retC);
		return retC; 
		//Cursor c = build.query(this.beerDataHelper.getReadableDatabase(), null , null, null, MetaBeer.INTERNAL_DATE_ADDED, null, MetaBeer.INTERNAL_DATE_ADDED + " desc");		
		//return c;
	}
	
	public Cursor getNewBeersChildViewCursor(BeerFilter beerFilter) {
		
		String tret2 = "strftime('%m-%d-%Y', datetime( (" + MetaBeer.INTERNAL_DATE_ADDED +"/1000) , 'unixepoch', 'localtime') ) as " + MetaBeer.SIMPLE_INTERNAL_DATE_2;
		
		String tret = "round(" + MetaBeer.INTERNAL_DATE_ADDED + "/(1000*60*60*24) ) as " + MetaBeer.SIMPLE_INTERNAL_DATE +" "; //as simpleinternal
		SQLiteQueryBuilder build =  beerFilter.getSQLBuilder(TABLE_NAME);
		build.setTables(TABLE_NAME);
		
		//String sqlraw = build.buildQuery(new String[] {tret, MetaBeer.DB_ID, MetaBeer.NAME, MetaBeer.STYLE_ID, MetaBeer.TASTED, MetaBeer.SIMPLE_DATE, MetaBeer.DESCRIPTION, MetaBeer.NAME, MetaBeer.BEER_ID, MetaBeer.INTERNAL_DATE_ADDED, MetaBeer.STATUS  }, null, null, null, null, MetaBeer.NAME, null);
		Cursor retC = build.query(
			this.beerDataHelper.getReadableDatabase(), 
			new String[] {
							tret, tret2, MetaBeer.DB_ID, MetaBeer.NAME, 
							MetaBeer.BEER_ID, MetaBeer.INTERNAL_DATE_ADDED, MetaBeer.NAME, 
							MetaBeer.DRAUGHT, MetaBeer.CAN, MetaBeer.BOTTLE  
						},
			null , 
			null, 
			null, 
			null, 
			MetaBeer.NAME, 
			null );//"simpleinternal"
		return retC; 
	}
	
	public Cursor getGroupedChildView(BeerFilter beerFilter) {
		SQLiteQueryBuilder build =  beerFilter.getSQLBuilder(TABLE_NAME);
		//build.setDistinct(true);
		//String propertyIn = beerFilter.getGroupBy();
//		Boolean grouped = false;
//		if (propertyIn != null) {
//			grouped = true;
//		}
		
		//String tret = "round(" + MetaBeer.INTERNAL_DATE_ADDED + "/(1000*60*60*24) ) as " + MetaBeer.SIMPLE_INTERNAL_DATE +" ";
		String[] someProjection = new String[] {MetaBeer.DB_ID, MetaBeer.NAME, MetaBeer.TASTED, MetaBeer.SIMPLE_DATE, MetaBeer.BEER_ID, MetaBeer.BOTTLE, MetaBeer.CAN, MetaBeer.DRAUGHT  };
		//String groupBy = null;
		String sortBy = MetaBeer.NAME;

		Cursor retC = build.query(this.beerDataHelper.getReadableDatabase(), someProjection, null , null, null, null, sortBy , null );//"simpleinternal" 
		return retC; 
	}
	
	//query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
	public Cursor getIncompleteBeers() {
		Cursor cursor = this.beerDataHelper.getReadableDatabase().query(
				TABLE_NAME, 
				new String[] { MetaBeer.BEER_ID, MetaBeer.DB_ID, MetaBeer.DESCRIPTION, "ifnull(" + MetaBeer.DESCRIPTION +",'') != '' as " + MetaBeer.DESCRIPTION_VALID }, 
				MetaBeer.DESCRIPTION_VALID + " = 0 ", null,
//				MetaBeer.BEER_ID + " = ? ", new String[] { "4389" },
//				null, null, 
				null, null,  
				null);
		return cursor;
	}
	
	public int updateExtraBeerInfo(ContentValues cv) {
		Integer curBeerId = cv.getAsInteger(MetaBeer.BEER_ID);
		SQLiteDatabase curDB = this.getBeerDataHelper().getWritableDatabase();
		int res = curDB.update(
				TABLE_NAME, 
				cv, 
				//sqlAnd( 
					makeSqlArg(MetaBeer.BEER_ID,curBeerId),
					//makeSqlArg(UserBeer.USER_ID,userDBID)
					//),
				null);
		return res;
	}
	
	//XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
	// Utilities
	//XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
		
	private String trimSort(String propertySorted) {
		String groupProperty = null;
		if (propertySorted.contains(" ")) {
			groupProperty = propertySorted.substring(0,propertySorted.lastIndexOf(" "));
		} else {
			groupProperty = propertySorted;
		}
		return groupProperty;
	}
	
/*	private Boolean isObjEmpty(Object e) {
		return (e == null || e.toString().length()==0);
	}
	
	private Boolean isObjEmpty(Date e) {
		return (e == null || e.getTime() == 0);
	}*/
	
	public Command getDateCommand(Operator oin, Date din ) {
		return new Command(MetaBeer.INTERNAL_DATE_ADDED, String.valueOf(din.getTime()), oin);
	}
	
	//executing this will cause a db creation by being called from UfoClubApp's global retrieval system
	public Boolean isEmpty() {
		Boolean ret = true;
		Cursor c = getBeerList();
		if (c.getCount() > 0) {
			ret = false;
		}
		c.close();
		return ret;
	}
	
	public void close() {
		if (this.beerDataHelper != null) {
			this.beerDataHelper.close();	
		}
	}
	
	public BeerDataHelperBase getBeerDataHelper() {
		return beerDataHelper;
	}

	public static Map<Integer, ContentValues> generateKeyMap(List<ContentValues> userBeerListIn) {
		Map<Integer,ContentValues> t = new HashMap<Integer, ContentValues>(userBeerListIn.size());
		
		for (ContentValues cv: userBeerListIn) {
			int beerId = cv.getAsInteger(MetaBeer.BEER_ID);
			t.put(beerId, cv);
//			Log.d(TAG,"beerId:cv - " + beerId + " " + cv.toString());
		}
		return t;
	}

	public static String sqlNotNull(Object conditional, String returnValue) {
		return conditional != null ? returnValue : "";
	}
	
	public static String makeSqlArg(String property, Object value) {
		return sqlNotNull(value,sqlEquals(property,value));
	}
	
	public static String sqlEquals(String arg1, Object arg2) {
		//return beerID != null ? (UserBeer.BEER_ID + " = " + beerID) : "";
		return arg1 + " = " + arg2.toString();
	}




	
	

}
